import logging
from datetime import datetime

from fastapi import APIRouter, Query, Path
from fastapi.params import Depends
from pydantic import Field, validator, BaseModel
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db

data_point_router = APIRouter(prefix="/data/room", tags=["位置管理"])


class RoomSearch(PaginationBase):
    fsiteNo: str = Field(None, description="所属站点")
    fpointNo: str = Field(None, description="监测点编号")
    fparentNo: str = Field(None, description="上级监测点")
    fpointFlag: str = Field(None, description="站点类型(0:教室,1:室外,2:教学楼)")
    fpointState: str = Field(None, description="状态(1正常/0停用)")


# todo 2.实验楼 4.房间信息
@data_point_router.get("/list", summary="实验楼查询")
def list_data_point(room: RoomSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from a_ts_point a "
    sql = sql + " left join a_ts_point b on b.fpoint_no=a.fparent_no"
    sql = sql + " where 1=1 "
    if room.fsiteNo is not None:
        sql = sql + " and a.fsite_no = :fsiteNo "
        param["fsiteNo"] = room.fsiteNo
    if room.fpointNo is not None:
        sql = sql + " and a.fpoint_no = :fpointNo "
        param["fpointNo"] = room.fpointNo
    if room.fparentNo is not None:
        sql = sql + " and a.fparent_no = :fparentNo "
        param["fparentNo"] = room.fparentNo
    if room.fpointFlag == "3":
        sql = sql + " and a.fpoint_flag >0 "
    if room.fpointFlag == "1" or room.fpointFlag == "2":
        sql = sql + " and a.fpoint_flag = :fpointFlag "
        param["fpointFlag"] = room.fpointFlag

    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select a.fpoint_no,a.fpoint_name,a.fparent_no,a.fsite_no,a.fpoint_flag,"
    sql = sql + " a.fpoint_level,a.fpoint_l,a.fpoint_d,a.fpoint_h,a.fpoint_w,a.fimage,"
    sql = sql + " a.fvideo_url,a.fperson,a.fpoint_state,a.fupddate,a.foperator,"
    sql = sql + " a.flayer_no,a.fcustnum,a.fcharge,a.fchargeprice,a.fsize,"
    sql = sql + " a.fappno,a.fmjtype,b.fpoint_no fparentNo,b.fpoint_name fparentName"
    sql = sql + " from a_ts_point a "
    sql = sql + " left join a_ts_point b on b.fpoint_no=a.fparent_no"
    sql = sql + " where 1=1 "
    # if point_State !="":
    #    sql = sql + " and fpoint_state = "+str(point_State)
    if room.fsiteNo is not None:
        sql = sql + " and a.fsite_no = :fsiteNo "
        param["fsiteNo"] = room.fsiteNo
    if room.fpointNo is not None:
        sql = sql + " and a.fpoint_no = :fpointNo "
        param["fpointNo"] = room.fpointNo
    if room.fparentNo is not None:
        sql = sql + " and a.fparent_no = :fparentNo "
        param["fparentNo"] = room.fparentNo
    if room.fpointFlag == "3":
        sql = sql + " and a.fpoint_flag >0 "
    if room.fpointFlag == "1" or room.fpointFlag == "2":
        sql = sql + " and a.fpoint_flag = :fpointFlag "
        param["fpointFlag"] = room.fpointFlag
    sql = sql + " order by  a.fpoint_no "
    sql = sql + " limit :limit offset :offset"
    param["offset"] = room.offset
    param["limit"] = room.limit

    data = db.execute(text(sql), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fpointNo'] = data[key][0]
        d1['fpointName'] = data[key][1]
        d1['fparentNo'] = data[key][2]
        d1['fsiteNo'] = data[key][3]
        d1['fpointFlag'] = data[key][4]
        d1['fpointLevel'] = data[key][5]
        d1['fpointL'] = data[key][6]
        d1['fpointD'] = data[key][7]
        d1['fpointH'] = data[key][8]
        d1['fpointW'] = data[key][9]
        d1['fimage'] = data[key][10]
        d1['fvideoUrl'] = data[key][11]
        d1['fperson'] = data[key][12]
        d1['fpointState'] = data[key][13]
        d1['fupddate'] = data[key][14]
        d1['foperator'] = data[key][15]
        d1['flayerNo'] = data[key][16]
        d1['fcustnum'] = data[key][17]
        d1['fcharge'] = data[key][18]
        d1['fchargeprice'] = data[key][19]
        d1['fsize'] = data[key][20]
        d1['fappno'] = data[key][21]
        d1['fmjtype'] = data[key][22]
        # d1['fparentno'] = data[key][23]
        d1['fparentName'] = data[key][24]
        d1['flayerName'] = data[key][24]
        data_list.append(d1)

    info_dict = {"list": data_list, "limit": room.limit, "page": room.pageIndex,
                 "pages": (count + room.limit - 1) // room.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class LabPointCreate(BaseModel):
    """实验室资料创建模型"""
    fpointNo: str = Field(..., description="实验室编号")
    fpointName: str = Field(..., description="实验室名称")
    fparentNo: str = Field(..., description="父级编号")
    fsiteNo: str = Field(..., description="站点编号")
    fpointFlag: int = Field(..., description="实验室标识")
    fpointState: int = Field(..., description="实验室状态")
    fupddate: datetime = Field(datetime.now(), description="更新日期")
    foperator: str = Field(..., description="操作人")

    # 坐标信息（默认0）
    fpointL: float = Field(0, description="经度")
    fpointD: float = Field(0, description="纬度")
    fpointH: float = Field(0, description="高度")
    fpointW: float = Field(0, description="宽度")

    # 可选字段
    fvideoUrl: str = Field("", description="视频URL")
    flayerno: str = Field("", description="图层编号")
    fimage: str = Field("", description="图片路径")


@data_point_router.post("/create", summary="新增实验楼")
def create_data_point(point: LabPointCreate, db: Session = Depends(get_db)):
    try:
        with db.begin():
            sql = "select fpoint_no from a_ts_point where fpoint_no= :fpointNo "
            data = db.execute(text(sql), {"fpointNo": point.fpointNo}).fetchall()

            if len(data) > 0:
                return BaseResponse.error(message="编号已存在")
            else:
                sql = "insert into a_ts_point(fpoint_no,fpoint_name,fparent_no,fsite_no,fpoint_flag,"
                sql = sql + "fpoint_level,fpoint_l,fpoint_d,fpoint_h,fpoint_w,fvideo_url,fperson,fpoint_state,fupddate,foperator,flayer_no,fcustnum,fcharge,fchargeprice,fsize,fappno,fmjtype)"
                sql = sql + (
                    " values( :fpointNo , :fpointName , :fparentNo , :fsiteNo , :fpointFlag ,0, :fpointL , :fpointD , :fpointH , :fpointW ,"
                    " :fvideourl , :foperator , :fpointState , :fupddate , :foperator , :flayerno ,0,0,0,1,1,0);")
                db.execute(text(sql), {
                    "fpointNo": point.fpointNo,
                    "fpointName": point.fpointName,
                    "fparentNo": point.fparentNo,
                    "fsiteNo": point.fsiteNo,
                    "fpointFlag": point.fpointFlag,
                    "fpointL": point.fpointL,
                    "fpointD": point.fpointD,
                    "fpointH": point.fpointH,
                    "fpointW": point.fpointW,
                    "fvideourl": point.fvideoUrl,
                    "foperator": point.foperator,
                    "fpointState": point.fpointState,
                    "fupddate": point.fupddate,
                    "flayerno": point.flayerno,
                })
                # 返回
                d1 = {}
                d1['fpointNo'] = point.fpointNo
                d1['fpointName'] = point.fpointName
                d1['fparentNo'] = point.fparentNo
                d1['fsiteNo'] = point.fsiteNo
                d1['fpointFlag'] = 2
                d1['fpointLevel'] = 0
                d1['fpointL'] = point.fpointL
                d1['fpointD'] = point.fpointD
                d1['fpointH'] = point.fpointH
                d1['fpointW'] = point.fpointW
                d1['fimage'] = None
                d1['fvideourl'] = point.fvideoUrl
                d1['fperson'] = None
                d1['fpointState'] = point.fpointState
                d1['fupddate'] = point.fupddate
                d1['foperator'] = point.foperator
                d1['flayerno'] = None
                d1['fcustnum'] = 0
                d1['fcharge'] = 0
                d1['fchargeprice'] = 0
                d1['fsize'] = 0
                d1['fappno'] = None
                d1['fmjtype'] = None
                return BaseResponse.success(data=d1)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@data_point_router.post("/update", summary="实验室修改接口")
def update_data_point(point: LabPointCreate, db: Session = Depends(get_db)):
    try:
        with db.begin():
            sql = "update a_ts_point"
            sql = sql + " set fpoint_name= :fpointName ,fparent_no= :fparentNo "
            sql = sql + ",fpoint_l= :fpointL ,fpoint_d= :fpointD ,fpoint_h= :fpointH ,fpoint_w= :fpointW "
            sql = sql + ",flayer_no= :flayerno ,fvideo_url= :fvideourl ,fpoint_Flag= :fpointFlag "
            sql = sql + ",fimage= :fimage "
            sql = sql + ",fpoint_State= :fpointState ,fupddate= :fupddate ,foperator= :foperator "
            sql = sql + " where fpoint_no= :fpointNo  and fsite_no= :fsiteNo "
            db.execute(text(sql), {
                "fpointName": point.fpointName,
                "fparentNo": point.fparentNo,
                "fpointL": point.fpointL,
                "fpointD": point.fpointD,
                "fpointH": point.fpointH,
                "fpointW": point.fpointW,
                "flayerno": point.flayerno,
                "fvideourl": point.fvideoUrl,
                "fpointFlag": point.fpointFlag,
                "fimage": point.fimage,
                "fpointState": point.fpointState,
                "fupddate": point.fupddate,
                "foperator": point.foperator,
                "fpointNo": point.fpointNo,
                "fsiteNo": point.fsiteNo
            })
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@data_point_router.post("/delete/{fpointNo}", summary="删除实验室")
def delete_data_point(fpointNo: str = Path(description="站点编号"), db: Session = Depends(get_db)):
    try:
        with db.begin():
            sql = "select sum(f1) "
            sql = sql + " from ( select count(1) f1 from a_ts_point where fparent_no= :fpointNo "
            sql = sql + " union"
            sql = sql + " select count(1) f1 from s_tc_equipment_inv where fpoint_no= :fpointNo ) a"
            sql = sql + " where 1=1"
            data = db.execute(text(sql), {
                "fpointNo": fpointNo,
            }).fetchall()

            if data[0][0] > 0:
                return BaseResponse.error(message="该资料在使用，不能删除")
            else:
                sql = "delete from a_ts_point where fpoint_no= :fpointNo "
                db.execute(text(sql), {
                    "fpointNo": fpointNo,
                })
                return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


class PointSearch(BaseModel):
    fsiteNo: str = Field("", description="站点编号")
    fpointFlag: str = Field("", description="站点标记")
    flayerNo: str = Field("", description="楼层编号")


@data_point_router.get("/list2", summary="实验楼查询接口")
def list2_data_point(point: PointSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select fpoint_no,fpoint_name,fparent_no,fsite_no,fpoint_flag,"
    sql = sql + " fpoint_level,fpoint_l,fpoint_d,fpoint_h,fpoint_w,fimage,"
    sql = sql + " fvideo_url,fperson,fpoint_state,fupddate,foperator,flayer_no,"
    sql = sql + " fcustnum,fcharge,fchargeprice,fsize,fappno,fmjtype"
    sql = sql + " from a_ts_point a "
    sql = sql + " where 1=1"
    if point.fsiteNo != "":
        sql = sql + " and a.fsite_no = :site_no "
        param["site_no"] = point.fsiteNo
    if point.fpointFlag != "":
        sql = sql + " and a.fpoint_flag = :point_Flag "
        param["point_Flag"] = point.fpointFlag
    if point.flayerNo != "":
        sql = sql + " and a.flayer_no = :flayerNo "
        param["flayerNo"] = point.flayerNo
    sql = sql + " order by a.fpoint_no"
    data = db.execute(text(sql), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fpointNo'] = data[key][0]
        d1['fpointName'] = data[key][1]
        d1['fparentNo'] = data[key][2]
        d1['fsiteNo'] = data[key][3]
        d1['fpointFlag'] = data[key][4]
        d1['fpointLevel'] = data[key][5]
        d1['fpointL'] = data[key][6]
        d1['fpointD'] = data[key][7]
        d1['fpointH'] = data[key][8]
        d1['fpointW'] = data[key][9]
        d1['fimage'] = data[key][10]
        d1['fvideoUrl'] = data[key][11]
        d1['fperson'] = data[key][12]
        d1['fpointState'] = data[key][13]
        d1['fupddate'] = data[key][14]
        d1['foperator'] = data[key][15]
        d1['flayerNo'] = data[key][16]
        d1['fcustnum'] = data[key][17]
        d1['fcharge'] = data[key][18]
        d1['fchargeprice'] = data[key][19]
        d1['fsize'] = data[key][20]
        d1['fappno'] = data[key][21]
        d1['fmjtype'] = data[key][22]
        d1['ftaskno'] = None
        d1['fpointNoOrName'] = None
        d1['equipmentNumber'] = 0
        d1['fparentName'] = None
        d1['flayerName'] = None
        atsPointList = []
        d1['atsPointList'] = atsPointList
        data_list.append(d1)
    info_dict = {"list": data_list}
    return BaseResponse.success(data=info_dict)
